/*
Navicat MySQL Data Transfer

Source Server         : my
Source Server Version : 50610
Source Host           : localhost:3306
Source Database       : xingcheng

Target Server Type    : MYSQL
Target Server Version : 50610
File Encoding         : 65001

Date: 2017-09-04 12:20:17
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `examresult`
-- ----------------------------
DROP TABLE IF EXISTS `examresult`;
CREATE TABLE `examresult` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `exam_date` date DEFAULT NULL,
  `score` float(6,2) NOT NULL,
  `st_id` int(10) unsigned DEFAULT NULL COMMENT '考试学生',
  `sb_id` int(10) unsigned DEFAULT NULL COMMENT '考试的科目',
  PRIMARY KEY (`id`)
  KEY `fk_stu` (`st_id`),
  KEY `fk_subject` (`sb_id`),
  CONSTRAINT `fk_stu` FOREIGN KEY (`st_id`) REFERENCES `student` (`stuno`),
  CONSTRAINT `fk_subject` FOREIGN KEY (`sb_id`) REFERENCES `subject` (`id`)
) ;

-- ----------------------------
-- Records of examresult
-- ----------------------------
INSERT INTO examresult VALUES ('1', '2017-09-01', '90.00', '1', '3');

-- ----------------------------
-- Table structure for `grade`
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `gname` varchar(100) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`id`),
  UNIQUE KEY `gname` (`gname`)
) ;

-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO grade VALUES ('5', 'Java');
INSERT INTO grade VALUES ('1', 'S1');
INSERT INTO grade VALUES ('2', 'S2');
INSERT INTO grade VALUES ('3', 'Y2');

-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `stuno` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sname` varchar(40) NOT NULL COMMENT '姓名',
  `sex` enum('男','女','妖','人妖') DEFAULT '男' COMMENT '性别',
  `telephone` varchar(15) DEFAULT NULL COMMENT '电话',
  `qq` varchar(20) DEFAULT NULL COMMENT 'QQ号',
  `wechat` varchar(50) DEFAULT NULL COMMENT '微信',
  `gradeid` int(20) unsigned NOT NULL COMMENT '年级',
  `enterdate` date DEFAULT NULL COMMENT '入学日期',
  `fromcity` varchar(100) DEFAULT NULL COMMENT '从那个城市来的',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `address` varchar(400) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`stuno`)
 
) ;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO student VALUES ('1', '王武', '男', '131800433221', '14574934', null, '1', null, '阜阳', null, '阜阳');
INSERT INTO student VALUES ('2', '王莽', '男', '131800433221', null, null, '2', '2017-03-10', '合肥', null, null);
INSERT INTO student VALUES ('3', '李星', '女', '131800433221', '1949944', null, '3', '2017-04-10', '合肥', null, '阜阳');
INSERT INTO student VALUES ('5', '李八', '女', '131800433221', '666666', null, '3', '2017-04-20', '六安', null, '阜阳颖州区');
INSERT INTO student VALUES ('6', '牛三', '妖', '13590812397', '14574934', '14574934', '2', '2017-04-21', '六安', null, null);
INSERT INTO student VALUES ('7', '丁玉锁', '男', null, null, null, '5', null, '阜阳', '1995-05-01', null);
INSERT INTO student VALUES ('8', '丁钢锁', '男', null, null, null, '5', null, '阜阳', '1997-05-01', null);

-- ----------------------------
-- Table structure for `subject`
-- ----------------------------
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '课程标号',
  `subjectname` varchar(100) NOT NULL COMMENT '课程名称',
  `classhour` int(10) unsigned DEFAULT NULL COMMENT '课时',
  `gradeid` int(10) unsigned NOT NULL COMMENT '年级编号',
  PRIMARY KEY (`id`)
 
);

-- ----------------------------
-- Records of subject
-- ----------------------------
INSERT INTO subject VALUES ('5', 'java 基础', '16', '5');
INSERT INTO subject VALUES ('11', 'java oop', '20', '2');
INSERT INTO subject VALUES ('4', 'java高级特性', '8', '5');
INSERT INTO subject VALUES ('6', '利用html+css设计网页', '19', '3');
INSERT INTO subject VALUES ('14', '使用jquery实现网页特效', '12', '5');
INSERT INTO subject VALUES ('15', '数据库编程', '12', '5');
INSERT INTO subject VALUES ('16', 'java web编程', '16', '5');
INSERT INTO subject VALUES ('17', '深入mybatis', '10', '5');
INSERT INTO subject VALUES ('18', 'spring详解', '8', '5');
INSERT INTO subject VALUES ('19', 'struts2初步', '3', '5');
INSERT INTO subject VALUES ('20', 'hibernate浅窥', '2', '5');



INSERT INTO examresult VALUES('1','2017-2-10','12.5','1','10'),('2','2017-3-11','13.5','2','3'),('3','2017-4-12','14.5','3','4');

INSERT INTO grade VALUES(1,'java42'),(2,'java43'),(3,'java44');

INSERT INTO student VALUES ('1', '颜良', '男', '13111111111', '111', '1111', '1', '2017-01-01', '河北', '1995-01-01', '六安'),
('2', '文丑', '男', '1322222222', '222', '2222', '2', '2017-02-02', '河北', '1995-02-02', '合肥'),
('3', '蔡文姬', '女', '133333333', '333', '3333', '3', '2017-03-03', '洛阳', '1995-03-03', '陈留县');

INSERT INTO student VALUES ('4', '甄姬', '女', '13444444', '444', '4444', '4', '2017-04-30', '洛阳', '1995-01-01', '长安');

INSERT INTO student VALUES ('5', '张辽', '男', '135555555', '555', '5555', '5', '2017-05-31', '西凉', '1995-05-31', '逍遥津');

INSERT INTO subject VALUES ('1', '语文', '20', '3'),('2', '数学', '16', '6'),('3', '英语', '30', '4');

SELECT * FROM student WHERE sex='男' AND NOT address='六安';

SELECT *
FROM `student`
WHERE (MONTH(birthday)=1 and DAY(birthday)=31) or
(MONTH(birthday)=2 and DAY(birthday)=28) or
(MONTH(birthday)=2 and DAY(birthday)=29) or
(MONTH(birthday)=3 and DAY(birthday)=31) or
(MONTH(birthday)=4 and DAY(birthday)=30) or
(MONTH(birthday)=5 and DAY(birthday)=31) or
(MONTH(birthday)=6 and DAY(birthday)=30) or
(MONTH(birthday)=7 and DAY(birthday)=31) or
(MONTH(birthday)=8 and DAY(birthday)=31) or
(MONTH(birthday)=9 and DAY(birthday)=30) or
(MONTH(birthday)=10 and DAY(birthday)=31) or
(MONTH(birthday)=11 and DAY(birthday)=30) or
(MONTH(birthday)=12 and DAY(birthday)=31);




SELECT * FROM student;

SELECT AVG(score) from `examresult`;

SELECT count(id) from `examresult`;

SELECT * from `examresult` WHERE st_id=3 ;

SELECT * from `examresult` WHERE  ORDER BY  ;

SELECT * FROM student WHERE sex='女' ORDER BY birthday ASC LIMIT 1,6;

SELECT AVG(score) AS 平均分,MAX(score)AS 最高分,MIN(score)AS 最低分 from `examresult` WHERE exam_date='2016-01-01';

SELECT * FROM student WHERE birthday=(SELECT Max(birthday) FROM student);

SELECT * from student WHERE stuno IN(
SELECT id FROM `subject` WHERE subjectname='java 基础'
)

SELECT * from student WHERE stuno IN(
SELECT st_id FROM `examresult` WHERE sb_id IN(
SELECT id FROM `subject` WHERE subjectname='java 基础'
)AND exam_date=(
SELECT MAX(exam_date)
)
)

SELECT subjectname FROM subject WHERE gradeid IN(
SELECT id FROM grade WHERE id='s1'

)

SELECT `sname` FROM `student` 
WHERE `stuNo` NOT IN (
      SELECT `st_id` FROM `examresult` 
      WHERE `st_id` = (
          SELECT `id` FROM `subject` 
          WHERE `subjectName`='HTML'
       )
)




--第三题 
SELECT * FROM student WHERE (YEAR(birthday)>=1980 and MONTH(birthday)>=1 and DAY(birthday)>=1)
 AND (YEAR(birthday)<=1989 and MONTH(birthday)<=12 and DAY(birthday)<=31);


--第四题
SELECT * FROM student WHERE  fromcity IN(
SELECT fromcity FROM student WHERE fromcity='合肥'
)AND stuNo IN(
SELECT st_id FROM examresult WHERE score<60
)



SELECT * FROM examresult WHERE EXISTS(
SELECT stuno FROM student WHERE gradeid=(
SELECT id FROM grade WHERE gname='s2'
)
)AND sb_id IN(
SELECT id FROM `subject` WHERE gradeid IN (
SELECT id FROM grade WHERE gname='s2'
)
)


SELECT stuno, COUNT(stuno),sex,MAX(birthday),MIN(birthday) FROM student GROUP BY sex;

SELECT fromcity,sex,COUNT(*) FROM student GROUP BY fromcity,sex;

SELECT DISTINCT fromcity,sex,COUNT(*) FROM student

SELECT fromcity,COUNT(*) FROM student GROUP BY fromcity HAVING COUNT(*)>=2;

SELECT stuno,sname FROM student
UNION
SELECT id,subjectname FROM `subject`

SELECT s.stuno,s.fromcity,e.score FROM student as s LEFT JOIN examresult as e ON(s.stuno=e.st_id);

SELECT g.id,g.gname,s.subjectname,s.classhour,s.id FROM grade g LEFT JOIN `subject` s ON(g.id=s.gradeid);

SELECT g.id,g.gname,s.subjectname,s.classhour,s.id FROM grade g RIGHT JOIN `subject` s ON(g.id=s.gradeid);




--1:查询累计购买考试次数 超过3次的学生信息（全部）。
SELECT *,COUNT(stuno) FROM student AS s LEFT JOIN examresult as e ON(s.stuno=e.st_id) GROUP BY stuno HAVING COUNT(stuno)>3

--2：查询有过补考的学生信息（同一门课超过2次，认为是补考）
SELECT s.sname AS '姓名',sub.id AS '科目姓名',COUNT(e.st_id) AS '次数' FROM examresult AS e LEFT JOIN `student` as s ON(s.stuno=e.st_id) 
LEFT JOIN `subject` AS sub ON(e.sb_id=sub.id) 
GROUP BY s.stuno,sub.id HAVING COUNT(e.st_id)>2

SELECT * FROM student WHERE stuno IN(SELECT st_id FROM examresult GROUP BY sb_id,st_id HAVING count(*)>2);

--3：最近半年内入学的学生
 SELECT * FROM student WHERE (YEAR(enterdate)=YEAR(NOW()) AND MONTH(enterdate)>MONTH(NOW())-6)

--4: 查询年级信息，以及改年级的课程数，没有课程的也要显示，对应的课程数目为0
 
SELECT g.gname,COUNT(s.id) FROM `subject` AS s RIGHT JOIN grade AS g ON(g.id=s.gradeid) GROUP BY g.gname
SELECT g.gname,COUNT(s.id) FROM `grade` AS g LEFT JOIN `subject` AS s ON(g.id=s.gradeid) GROUP BY g.gname
SELECT id AS 年级,COUNT(gradeid) AS 课程数 FROM(
	SELECT G.id,S.gradeid FROM grade AS G LEFT OUTER JOIN subject AS S ON G.id=S.gradeid
)t GROUP BY id

SELECT g.*, (SELECT COUNT(*)  FROM `subject` s WHERE g.id=s.gradeid) AS 课程数 FROM `grade` g 


SELECT g.gname,s.subjectname FROM grade AS g ,`subject` AS s WHERE g.id=s.gradeid

SELECT s.sname,sub.subjectname,g.gname,e.exam_date,e.score FROM examresult AS e LEFT JOIN `student` as s ON(s.stuno=e.st_id) 
LEFT JOIN `subject` AS sub ON(e.sb_id=sub.id) 
LEFT JOIN grade AS g ON(g.id=sub.gradeid)
GROUP BY s.stuno,sub.subjectname


SELECT * FROM student WHERE telephone LIKE '1%'

-- SELECT id (SELECT birthday FROM student WHERE max(birthday)) FROM grade


SELECT *,gradeid,MAX(birthday) FROM student GROUP BY gradeid;

SELECT *,MAX(birthday) FROM student WHERE gradeid IN(
SELECT id FROM grade 
)GROUP BY gradeid

SELECT * FROM student WHERE (gradeid,birthday) IN(
SELECT gradeid,MAX(birthday) FROM student GROUP BY gradeid
)

SELECT * FROM student WHERE sname LIKE '刘%飞%'


BEGIN;

INSERT INTO examresult (score,st_id) VALUES(87,18),(101,28);

SELECT * FROM examresult;

ROLLBACK;
COMMIT;

CREATE VIEW stu AS
SELECT sname,qq,(YEAR(NOW())-YEAR(birthday)) AS 年龄 FROM student

SELECT * FROM stu;

DROP VIEW stuinfo;
CREATE VIEW stuinfo AS
SELECT score,(SELECT subjectname FROM `subject` su WHERE su.id=e.sb_id) AS 课程名,
AVG((SELECT stuno from student s WHERE e.st_id=s.stuno)) AS 平均分 FROM examresult e

USE information_schema;
SELECT * FROM stuinfo;




--
3:设计高分成绩单视图，只包含成绩在80分以上的学生信息，包括科目名称以及成绩

CREATE  VIEW stuscore
 AS

SELECT e.score,(SELECT subjectname FROM `subject` su WHERE su.id=e.sb_id ) AS 课程名 ,
(SELECT sname FROM student s WHERE s.stuno=e.st_id ) As 学生 FROM examresult e WHERE e.score>80 GROUP BY e.score

SELECT * FROM stuscore
